﻿using TradePublish.Common;
using log4net;
using TradePublish.Service;
using System;
using System.Data;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Collections;
using System.Data.OracleClient;

namespace TradePublish.Dao
{
    public class DissentTradeDao
    {
        private ILog log = null;

        public DissentTradeDao()
        {
            this.log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        }

        public Hashtable findTopUnGraspImg(int length)
        {
            #region #//无效代码
            //string str = "";
            //if ((expIds != null) && (expIds.Length > 0))
            //{
            //    String[] ids = expIds.ToString().Split(',');
            //    for (int xx = 0; xx < ids.Length; xx ++ )
            //    {
            //        if (ids[xx] == null || ids[xx].Equals(""))
            //            continue;
            //        str += " and id<>" + ids[xx];
            //    }
            //   // str = " and id not in (" + expIds.Remove(expIds.Length - 1, 1).ToString() + ") ";
            //}
            #endregion
            string commandText = string.Concat(new object[] { " select  * from (select reg_no ,CLASS_NO from  DISSENT_TRADE WHERE (HAS_GRASP_IMAGE=0 or HAS_GRASP_IMAGE is null )  order by  ID DESC  ) where ROWNUM<=" + length + " order by ROWNUM asc" });
            Hashtable  rtn = new Hashtable();
            DataSet set = new DataSet();
            while (true)
            {
                #region 6点到6点半暂停
                if (DateTime.Now.Hour == 6 && DateTime.Now.Minute < 30  || (DateTime.Now.Hour >=8 && DateTime.Now.Hour <18  &&  Convert.ToInt32(DateTime.Now.DayOfWeek) !=0 && Convert.ToInt32(DateTime.Now.DayOfWeek) !=6   ) )
                {
                    Thread.Sleep(180000);//3fenzhong  
                    continue;
                }
                #endregion
                DatabaseHelper dbh = new DatabaseHelper();
                try
                {
                    dbh.BeginTransaction();
                    set = dbh.ExecuteDataSet(commandText, TradePublish.Service.ConnectionState.KeepOpen);
                    if (set.Tables == null || set.Tables[0].Rows == null || set.Tables[0].Rows.Count == 0)
                    {
                        //无数据
                        dbh.CommitTransaction(); 
                        this.log.Info("读取数据库表DISSENT_TRADE，获得尚未采集商标图片的商标0个.");
                        return null;
                    }
                    this.log.Info("读取数据库表DISSENT_TRADE，获得尚未采集商标图片的商标" + set.Tables[0].Rows.Count + "个.");
                    //有数据。
                    StringBuilder updateSql = new StringBuilder("update dissent_trade set HAS_GRASP_IMAGE = 2 where ");
                    for (int i = 0; i < set.Tables[0].Rows.Count; i++)
                    {
                        String regNo = set.Tables[0].Rows[i][0].ToString();
                        String classNo = set.Tables[0].Rows[i][1].ToString();
                        if (!rtn.ContainsKey(regNo))
                        {
                            rtn.Add(regNo, classNo);
                        }

                        if (i != 0)
                        {
                            updateSql.Append(" or ");
                        }
                        updateSql.Append("reg_no='").Append(regNo).Append("'");
                    }
                    dbh.ExecuteNonQuery(updateSql.ToString(), TradePublish.Service.ConnectionState.KeepOpen);
                    dbh.CommitTransaction();
                    return rtn;
                }
                catch (Exception exception)
                {
                    dbh.RollbackTransaction();
                    this.log.Error("读取数据库表dissent_trade，获得本次的未采集图片的商标时发送异常", exception);
                    Thread.Sleep(300000);
                }
                finally
                {
                    dbh.Dispose();
                }

            }
        }

        ///**
        // * 服务端获得需要采集的申请人，一次获得1000个
        // * 异常自己处理。
        // * 
        // */
        //public Hashtable findUnSearchEmailTask()
        //{
        //   //先获得最大ISSUE   " select max(trade_publish_issue) from dissent_trade";
        //    Hashtable rtn = new Hashtable();
        //    DataSet set = new DataSet();
        //    String maxsql = "select max(TRADE_PUBLISH_ISSUE)  from   dissent_trade";

        //    String sql = "select *  from (select distinct apply_user_name from dissent_trade where length( apply_user_name) > 3 and trade_publish_issue=:maxISSUE  and HAS_SEARCH_EMAIL=0  order by apply_user_name desc  )   where rownum <11 ";
        //    String sqlEx = "select *  from (select distinct apply_user_name from dissent_trade where length( apply_user_name) > 3 and trade_publish_issue=:maxISSUE  and HAS_SEARCH_EMAIL=2  order by apply_user_name desc  )   where rownum <11 ";
        //    while (true)
        //    {
        //        //#region 6点到6点半暂停，不加时间限制了 
        //        //#endregion
        //         Int32 maxISSUE = 99999;
        //         #region 获得最大公告期
        //         while (true)
        //         {
        //             try
        //             {
        //                 DatabaseHelper dbhT = new DatabaseHelper();
        //                 set = dbhT.ExecuteDataSet(maxsql);
        //                 maxISSUE = Convert.ToInt32(set.Tables[0].Rows[0][0].ToString());
        //                 break;
        //             }
        //             catch (Exception exception)
        //             {
        //                 this.log.Error("读取数据库表dissent_trade，获得最大的公告期时发送异常", exception);
        //                 Thread.Sleep(3000); 
        //             }
        //         }
        //         #endregion

        //         DatabaseHelper dbh = new DatabaseHelper(); 
        //        try
        //        {
        //            while (true)
        //            {
        //                if (maxISSUE <1320)
        //                {
        //                    this.log.Info("读取数据库表dissent_trade，获得本次未采集邮箱的申请人名称任务0个.");
        //                    return rtn;
        //                }
        //                dbh.BeginTransaction();
        //                OracleParameter maxISSUEP = new OracleParameter(":maxISSUE", OracleType.Number);
        //                maxISSUEP.Value = maxISSUE;
        //                dbh.AddParameter(maxISSUEP);
        //                set = dbh.ExecuteDataSet(sql, TradePublish.Service.ConnectionState.KeepOpen);
        //                if (set == null || set.Tables == null || set.Tables.Count == 0 || set.Tables[0].Rows == null || set.Tables[0].Rows.Count == 0)
        //                {
        //                    //无数据，找本公告期中关闭节点未完任务
        //                    set = dbh.ExecuteDataSet(sqlEx, TradePublish.Service.ConnectionState.KeepOpen);
        //                    if (set == null || set.Tables == null || set.Tables.Count == 0 || set.Tables[0].Rows == null || set.Tables[0].Rows.Count == 0)
        //                    {
        //                        //无数据，继续下一个公告期
        //                        maxISSUE--;
        //                        dbh.CommitTransaction();
        //                    }
        //                    else
        //                    {
        //                        break;
        //                    } 
        //                }
        //                else
        //                {
        //                    break;
        //                }
        //            } 

        //            this.log.Info("读取数据库表dissent_trade，获得本次需采集邮箱的申请人名称任务" + set.Tables[0].Rows.Count + "个.");
        //            //有数据。
        //            StringBuilder updateSql = new StringBuilder("update dissent_trade set HAS_SEARCH_EMAIL = 2 where TRADE_PUBLISH_ISSUE=:maxISSUE and APPLY_USER_NAME=:app");
        //            for (int i = 0; i < set.Tables[0].Rows.Count;i++ )
        //            {
        //                if (!rtn.Contains(set.Tables[0].Rows[i][0].ToString()))
        //                {
        //                    rtn.Add(set.Tables[0].Rows[i][0].ToString(), ""+maxISSUE);
        //                } 

        //                OracleParameter maxISSUEP = new OracleParameter(":maxISSUE", OracleType.Number);
        //                maxISSUEP.Value = maxISSUE;
        //                dbh.AddParameter(maxISSUEP);

        //                OracleParameter appP = new OracleParameter(":app", OracleType.NVarChar);
        //                appP.Value = set.Tables[0].Rows[i][0].ToString();
        //                dbh.AddParameter(appP);
        //                log.Debug("<<<<<<<<<<<<" + updateSql.ToString() + "||||" + set.Tables[0].Rows[i][0].ToString());
        //                dbh.ExecuteNonQuery(updateSql.ToString(), TradePublish.Service.ConnectionState.KeepOpen);//这个耗时比较大
        //            }
                    
        //            dbh.CommitTransaction();
        //            return rtn;
        //        }
        //        catch (Exception exception)
        //        {
        //            dbh.RollbackTransaction();  
        //            this.log.Error("读取数据库表dissent_trade，获得本次的未采集邮箱的申请人名称时发送异常", exception);
        //            Thread.Sleep(5000); 
        //        }
        //        finally
        //        {
        //            dbh.Dispose();
        //        }
        //    }
        //}


        /**
        * 服务端获得需要采集的申请人，一次获得1000个
        * 异常自己处理。
        * 
        */
        public Hashtable findUnSearchEmailTask()
        {
            //先获得最大ISSUE   " select max(trade_publish_issue) from dissent_trade";
            Hashtable rtn = new Hashtable();
            DataSet set = new DataSet();
            String maxsql = "select max(TRADE_PUBLISH_ISSUE)  from   dissent_trade";

            String sql = "select * from( select distinct apply_user_name from dissent_trade where length( apply_user_name) > 3 and trade_publish_issue=:maxISSUE  and HAS_SEARCH_EMAIL=0  order by apply_user_name desc  )   where rownum <10000   ";
            String sqlEx = "select * from( select distinct apply_user_name from dissent_trade where length( apply_user_name) > 3 and trade_publish_issue=:maxISSUE  and HAS_SEARCH_EMAIL=2  order by apply_user_name desc  )   where rownum <10000  ";
            while (true)
            {
                //#region 6点到6点半暂停，不加时间限制了 
                //#endregion
                Int32 maxISSUE = 99999;
                #region 获得最大公告期
                while (true)
                {
                    try
                    {
                        DatabaseHelper dbhT = new DatabaseHelper();
                        set = dbhT.ExecuteDataSet(maxsql);
                        maxISSUE = Convert.ToInt32(set.Tables[0].Rows[0][0].ToString());
                        break;
                    }
                    catch (Exception exception)
                    {
                        this.log.Error("读取数据库表dissent_trade，获得最大的公告期时发送异常", exception);
                        Thread.Sleep(3000);
                    }
                }
                #endregion

                DatabaseHelper dbh = new DatabaseHelper();
                try
                {
                    while (true)
                    {
                        if (maxISSUE < 1320)
                        {
                            this.log.Info("读取数据库表dissent_trade，获得本次未采集邮箱的申请人名称任务0个.");
                            return rtn;
                        }
                        dbh = new DatabaseHelper();
                        OracleParameter maxISSUEP = new OracleParameter(":maxISSUE", OracleType.Number);
                        maxISSUEP.Value = maxISSUE;
                        dbh.AddParameter(maxISSUEP);
                        set = dbh.ExecuteDataSet(sql );
                        if (set == null || set.Tables == null || set.Tables.Count == 0 || set.Tables[0].Rows == null || set.Tables[0].Rows.Count == 0)
                        {
                            dbh = new DatabaseHelper();
                            //无数据，找本公告期中关闭节点未完任务
                            set = dbh.ExecuteDataSet(sqlEx );
                            if (set == null || set.Tables == null || set.Tables.Count == 0 || set.Tables[0].Rows == null || set.Tables[0].Rows.Count == 0)
                            {
                                //无数据，继续下一个公告期
                                maxISSUE--; 
                            }
                            else
                            {
                                break;
                            }
                        }
                        else
                        {
                            break;
                        }
                    }

                    this.log.Info("读取数据库表dissent_trade，获得本次需采集邮箱的申请人名称任务" + set.Tables[0].Rows.Count + "个."+maxISSUE);
                    //有数据。 
                    for (int i = 0; i < set.Tables[0].Rows.Count; i++)
                    {
                        if (!rtn.Contains(set.Tables[0].Rows[i][0].ToString()))
                        {
                            rtn.Add(set.Tables[0].Rows[i][0].ToString(), "" + maxISSUE);
                        } 
                    } 
                    return rtn;
                }
                catch (Exception exception)
                { 
                    this.log.Error("读取数据库表dissent_trade，获得本次的未采集邮箱的申请人名称时发送异常", exception);
                    Thread.Sleep(5000);
                }
                finally
                {
                    dbh.Dispose();
                }
            }
        }

        /// <summary>
        /// 获得已采集联系人列表 。将来用来更新反查记录
        /// </summary>
        /// <returns></returns>
        public DataSet findHasSearchEmail()
        {
            DataSet ds = new DataSet();
            String sql = "select id,apply_user_name from APPLU_USER_CONTRACT_TMP where has_grasped=1 order by id asc";
            DatabaseHelper dbh = new DatabaseHelper();
            while (true)
            {
                try
                {
                    ds = dbh.ExecuteDataSet(sql);
                    return ds;
                }
                catch (Exception exception)
                {
                    this.log.Error("保存数据库表APPLU_USER_CONTRACT_TMP，放入临时表异常", exception);
                    Thread.Sleep(5000);
                }
                finally
                {
                    dbh.Dispose();
                }
            } 
        }


        /// <summary>
        /// 将申请人存入临时表，作为服务器端任务
        /// </summary>
        public void saveTmp(Hashtable applyUserNames)
        {
            if (applyUserNames == null || applyUserNames.Count == 0)
            {
                return;
            }
            string str1 = "INSERT INTO APPLU_USER_CONTRACT_TMP (id,apply_user_name,issue,has_grasped) VALUES(gen_sequence.nextval,'";
            string str2 = "','";
            string str3= "',0)";

            while (true)
            {
                if (DateTime.Now.Hour == 6 && DateTime.Now.Minute < 30 || (DateTime.Now.Hour >= 8 && DateTime.Now.Hour < 18 && Convert.ToInt32(DateTime.Now.DayOfWeek) != 0 && Convert.ToInt32(DateTime.Now.DayOfWeek) != 6))
                {
                    Thread.Sleep(180000);//3fenzhong  
                    continue;
                }
                DatabaseHelper dbh = new DatabaseHelper();
                try
                {
                    dbh.BeginTransaction();
                    foreach (DictionaryEntry objDE in applyUserNames)
                    {
                        dbh.ExecuteNonQuery(str1 + objDE.Key.ToString() + str2 + objDE.Value.ToString() + str3, TradePublish.Service.ConnectionState.KeepOpen);
                    }
                    dbh.CommitTransaction();
                    return;
                }
                catch (Exception exception)
                {
                    dbh.RollbackTransaction();
                    this.log.Error("保存数据库表APPLU_USER_CONTRACT_TMP，放入临时表异常", exception);
                    Thread.Sleep(5000);
                }
                finally
                {
                    dbh.Dispose();
                }
            }

        }
        public void setHasGraspImage(string regNo, bool hasGrasped)
        {
            string commandText = "update  DISSENT_TRADE  set HAS_GRASP_IMAGE=1  WHERE reg_no='" + regNo + "'";
            string str2 = "INSERT INTO  DISSENT_TRADE_GRASPED_IMG (id,Dissent_reg_no) VALUES(gen_sequence.nextval,'" + regNo + "')";
            while (true)
            {
                if (DateTime.Now.Hour == 6 && DateTime.Now.Minute < 30  || (DateTime.Now.Hour >=8 && DateTime.Now.Hour <18  &&  Convert.ToInt32(DateTime.Now.DayOfWeek) !=0 && Convert.ToInt32(DateTime.Now.DayOfWeek) !=6   ) )
                {
                    Thread.Sleep(180000);//3fenzhong  
                    continue;
                }
                DatabaseHelper dbh = new DatabaseHelper();
                try
                {
                    dbh.ExecuteNonQuery(  commandText );
                    if (!hasGrasped)
                    {
                        dbh.ExecuteNonQuery ( str2 );
                    }
                    return;
                }
                catch (Exception exception)
                {
                    this.log.Error("保存数据库表DISSENT_TRADE，设置已采集图片异常", exception);
                    Thread.Sleep(0x1388);
                }
                finally
                {
                    dbh.Dispose();
                }
            }
        }

        public void setHasSearchEmail(string applyUserNames, String issue)
        {
            string commandText = " update dissent_trade SET  HAS_SEARCH_EMAIL = 1  where APPLY_USER_NAME ='" + applyUserNames.Replace("'", "''") + "' and trade_publish_issue="+issue;
            DataSet set = new DataSet();
            while (true)
            {
                if (DateTime.Now.Hour == 6 && DateTime.Now.Minute < 30  || (DateTime.Now.Hour >=8 && DateTime.Now.Hour <18  &&  Convert.ToInt32(DateTime.Now.DayOfWeek) !=0 && Convert.ToInt32(DateTime.Now.DayOfWeek) !=6   ) )
                {
                    Thread.Sleep(180000);//3fenzhong  
                    continue;
                }
                DatabaseHelper dbh = new DatabaseHelper();
                try
                {
                    log.Debug("<<<<<<<<<--" + commandText);
                   // dbh.BeginTransaction();
                  //  dbh.ExecuteNonQuery (commandText,TradePublish.Service.ConnectionState.KeepOpen);
                  //  dbh.CommitTransaction();
                    dbh.ExecuteNonQuery(commandText );
                    return;
                }
                catch (Exception exception)
                {
                    this.log.Error("设置申请人的邮箱已采集异常，dissent_trade", exception);
                }
                finally
                {
                    dbh.Dispose();
                }
                Thread.Sleep(0x1388);
            }
        }

        public bool verifyHasGrasped(string regNo)
        {
            string commandText = " select count(ID) from DISSENT_TRADE_GRASPED_IMG where Dissent_reg_no='" + regNo + "'";
            while (true)
            {
                if (DateTime.Now.Hour == 6 && DateTime.Now.Minute < 30  || (DateTime.Now.Hour >=8 && DateTime.Now.Hour <18  &&  Convert.ToInt32(DateTime.Now.DayOfWeek) !=0 && Convert.ToInt32(DateTime.Now.DayOfWeek) !=6   ) )
                {
                    Thread.Sleep(180000);//3fenzhong  
                    continue;
                }
                DataSet set = new DataSet();
                DatabaseHelper dbh = new DatabaseHelper();
                try
                {
                    set = dbh.ExecuteDataSet( commandText);

                    if (set.Tables != null && set.Tables.Count > 0 && set.Tables[0].Rows.Count > 0 && Convert.ToInt32(set.Tables[0].Rows[0][0]) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception exception)
                {
                    this.log.Error("验证某注册号是否已采集图片，内部异常.", exception);
                    Thread.Sleep(0x1388);
                }
                finally
                {
                    dbh.Dispose();
                }
            }
        }
    }
}
